const { Model, Schema } = require('../helps/model');
const { formatData } = require('../helps/tools');
const { executor } = require('../helps/model');
// CREATE TABLE `notify_record` (
//   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
//   `robot_id` int(11) unsigned NOT NULL COMMENT '机器人id',
//   `type` varchar(255) NOT NULL COMMENT '类型：spell，read',
//   `module` varchar(255) NOT NULL COMMENT '模块：letter，word，book_album，sound_voice',
//   `module_id` int(11) unsigned NOT NULL COMMENT '模块id',
//   `duration` int(11) unsigned NOT NULL COMMENT '时长统计，统计时做sum',
//   `percent` int(11) unsigned NOT NULL COMMENT '百分比数，统计时做avg',
//   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
//   PRIMARY KEY (`id`),
//   KEY `stat` (`type`,`create_time`,`module`) USING BTREE,
//   KEY `stat_top` (`type`,`module`,`module_id`) USING BTREE
// ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='统计记录表';

const schema = new Schema({
  primary: 'id',
  create_time: true,
  auto_increment: true,
  fields: ['id', 'robot_id', 'type', 'module', 'module_id', 'duration', 'percent'],
  default: {},
});

export const model = new Model('notify_record', schema);

/**
 * 查找
 * @param id
 * @returns {*}
 */
export function get(id) {
  return model.findByKey(id);
}

/**
 * 查找列表
 * @param ids
 * @returns {*}
 */
export function getIds(ids) {
  return model.findByQuery({
    where: [
      ['id', 'in', ids],
    ],
  });
}

/**
 * 创建
 * @param data
 * @returns {*}
 */
export function add(data) {
  return model.insert(formatData(schema.baseFields(), data));
}

/**
 * 更新
 * @param id
 * @param data
 * @returns {*}
 */
export function put(id, data) {
  return model.updateByKey(id, formatData(schema.baseFields(), data));
}

/**
 * 删除
 * @param id
 * @returns {*}
 */
export function del(id) {
  return model.deleteByKey(id);
}

/**
 * 列表分页
 * @param page
 * @param number
 * @returns {*}
 */
export function search(where, page = 1, number = 50, order = 'id', direction = 'desc') {
  return model.findListAndCount(where, page, number, `\`${order}\` ${direction}`);
}

export function list(page, number, order, direction) {
  return search([], page, number, order, direction);
}

export function day(robot_id, type, start_time, end_time) {
  let sql = `SELECT count(*) as \`count\`, sum(\`duration\`) as \`duration_sum\` from ${model.table_name} where create_time between '${start_time}' and '${end_time}' and \`robot_id\` = ${robot_id} and \`type\` = '${type}' group by date(create_time)`;
  return executor.exec(sql).then((result) => {
    return result.map((row) => {
      return {
        count: row.count,
        duration: row.duration_sum,
      };
    });
  });
}

export function top(robot_id, type, module, t) {
  let sql = `SELECT \`module\`, \`module_id\`, count(*) as \`count\`, sum(\`duration\`) as \`duration_sum\` from ${model.table_name} where \`robot_id\` = ${robot_id} and \`type\` = '${type}' and \`module\`='${module}' group by \`module_id\` order by count(*) limit ${t}`;
  return executor.exec(sql).then((result) => {
    return result.map((row) => {
      return {
        module: row.module,
        module_id: row.module_id,
        count: row.count,
        duration: row.duration_sum,
      };
    });
  });
}

export function aggregation(robot_id, type) {
  let sql = `SELECT \`module\`, count(DISTINCT \`module_id\`) as \`number\`, count(*) as \`count\`, sum(\`duration\`) as \`duration_sum\`, avg(\`percent\`) as percent_avg from ${model.table_name} where \`robot_id\` = ${robot_id} and \`type\` = '${type}' group by \`module\``;
  return executor.exec(sql).then((result) => {
    return result.map((row) => {
      return {
        module: row.module,
        count: row.count,
        duration: row.duration_sum,
        number: row.number,
        percent: row.percent_avg,
      };
    });
  });
}
